package news.dao;

import news.pojo.News;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class NewsDao {

    /**
     * 物理删除
     * 根据id删除新闻数据
     *
     * @param id
     * @return
     */
    public boolean delete(Integer id) throws SQLException {
        String sql = "delete from news where id = ?";
        int count = DBTool.getQueryRunner().execute(sql, id);
        return count > 0;
    }

    /**
     * 逻辑删除 设置状态为0
     *
     * @param id
     * @return
     * @throws SQLException
     */
    public int setDelete(Integer id) throws SQLException {
        String sql = "update news set status = 0 where id = ?";
        return DBTool.getQueryRunner().execute(sql, id);
    }

    // 新增
    public int insert(News news) throws SQLException {
        String sql = "insert into news (cate, title, content, source, author, pub_time, uuid) value (?,?,?,?,?,?,'self')";
        return DBTool.getQueryRunner().execute(sql,
                news.getCate(),
                news.getTitle(),
                news.getContent(),
                news.getSource(),
                news.getAuthor(),
                news.getPubTime());
    }

    // 修改
    public int update(News news) throws SQLException {
        String sql = "update news set cate=?, title=?, content=?, source=?, author=?, pub_time=? where id = ?";
        return DBTool.getQueryRunner().execute(sql,
                news.getCate(),
                news.getTitle(),
                news.getContent(),
                news.getSource(),
                news.getAuthor(),
                news.getPubTime(),
                news.getId());
    }

    public News selectOne(Integer id) throws SQLException {
        String sql = "select *,pub_time as pubTime from news where id=? and status=1";
        return DBTool.getQueryRunner().query(sql, new BeanHandler<News>(News.class), id);
    }

    public List<News> selectAll() throws SQLException {
        String sql = "select * from news where status =1";
        return DBTool.getQueryRunner().query(sql, new BeanListHandler<News>(News.class));
    }


    public List<News> selectByPage(int page, int pageSize,String title) throws SQLException {
        if(title == null) title = "";
        title = "%"+ title + "%";
        int start = (page - 1) * pageSize;//起始位置
        int size = pageSize;//每页条数
        String sql = "select nc.title as cate,n.id,n.title,n.source,n.author,n.pub_time as pubTime,n.level from news n " +
                "inner join news_cate nc on n.cate = nc.alias where status = 1 and n.title like ? limit ?,?";
        System.out.println(sql);
        return DBTool.getQueryRunner().query(sql, new BeanListHandler<News>(News.class), title,start, size);
    }
    public Long selectCount(String title) throws SQLException {
        if(title == null) title = "";
        title = "%"+ title + "%";
        String sql = "select count(1) as news_count from news where status = 1 and title like ?";
        System.out.println(sql);
        // 查询单个数据 使用ScalarHandler
        return DBTool.getQueryRunner().query(sql, new ScalarHandler<Long>(),title);
    }

    /**
     * 按照等级查询
     *
     * @param level
     * @param size
     * @return
     * @throws SQLException
     */
    public List<News> selectByLevel(int level, int size) throws SQLException {
        String sql = "select *,pub_time as pubTime from news where status =1 and level=? order by pub_time desc limit ?";
        return DBTool.getQueryRunner().query(sql, new BeanListHandler<News>(News.class), level, size);
    }
}
